package com.kz.service.impl;

import com.kz.entity.YcreBig;
import com.kz.entity.YcreCost;
import com.kz.mapper.YcreBigMapper;
import com.kz.service.IYCREService;
import com.kz.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.format.DateTimeFormatter;
import java.util.List;

@Service
public class YCREServiceImpl implements IYCREService {

    @Resource
    private YcreBigMapper ycreBigMapper;

    /**
     * 导入excel表格
     * post方法
     */
    @Override
    public String upload(MultipartFile file) throws Exception {
        return "";

        /*if (file == null || file.isEmpty()) {
            throw new Exception("导入文件不能为空");
        }

        // 获取文件名
        String fileName = file.getOriginalFilename();

        int len = fileName.lastIndexOf(".");
        if (len <= 0) {
            throw new Exception("文件类型有误,请确认!");
        }

        String fileType = fileName.substring(len + 1).toLowerCase();
        if (!fileType.equals("xls") && !fileType.equals("xlsx")) {
            throw new Exception("只允许上传xls、xlsx");
        }
        InputStream in = file.getInputStream();
        Workbook wb = ExcelUtil.chooseWorkbook(fileName, in);

        List<Company> companies = getCompaniesFromExcel(wb);

        List<String> creditCodes = companies.stream().map(Company::getCreditCode).collect(Collectors.toList());

        List<Company> exists = companyRepository.findCompaniesByCreditCodeIn(creditCodes);

        if (exists != null && !exists.isEmpty()) {
            throw new ResException("信用代码为" + com.sinoyd.common.util.StringExtendUtil.join(creditCodes, ",") + "的企业已存在");
        }
        companyRepository.save(companies);

        return "企业导入成功!";*/
    }

    /**
     * 导出excel表格
     * get方法
     *
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    @Override
    public String export(HttpServletRequest request, HttpServletResponse response, List<String> ids) throws Exception {

        if (ids == null || ids.size() == 0) {
            return "没有数据导出！";
        }

        List<YcreBig> companies = ycreBigMapper.selectYcreBigListByAccId(ids);

        if (companies == null || companies.size() == 0) {
            return "没有数据导出！";
        }

        HSSFWorkbook wb = new HSSFWorkbook();//创建Excel文件

        HSSFSheet sheet = wb.createSheet("数据表");

        String sheetName = sheet.getSheetName();
        wb.setSheetName(0, sheetName);
        sheet.setDefaultRowHeight((short) (20 * 20));
        CellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);

        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("位置号");//为第一个单元格设值
        row.createCell(1).setCellValue("销售");//为第二个单元格设值
        row.createCell(2).setCellValue("操作");//为第三个单元格设值
        row.createCell(3).setCellValue("品名");//为第三个单元格设值
        row.createCell(4).setCellValue("运输数量");//为第三个单元格设值
        row.createCell(5).setCellValue("应收");//cost
        row.createCell(6).setCellValue("应付");//cost
        row.createCell(7).setCellValue("费用类别");//cost
        row.createCell(8).setCellValue("客户");//cost
        row.createCell(9).setCellValue("利润");
        row.createCell(10).setCellValue("付款日期");//cost
        row.createCell(11).setCellValue("操作费");
        row.createCell(12).setCellValue("收款日期");
        row.createCell(13).setCellValue("业务提成");
        row.createCell(14).setCellValue("提成比例");
        row.createCell(15).setCellValue("Open Account");
        row.createCell(16).setCellValue("备注");

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");


        int i = 0;
        for (YcreBig company : companies) {

            List<YcreCost> costList = company.getCostList();

            if (costList != null && costList.size() > 0) {
                for (YcreCost ycreCost : costList) {
                    i++;

                    row = sheet.createRow(i);
                    row.createCell(0).setCellValue(company.getLocationId() == null ? "" : company.getLocationId());
                    row.createCell(5).setCellValue(ycreCost.getReceivable() == null ? "0" : "(￥" + ycreCost.getReceivable() + ".00)");
                    row.createCell(6).setCellValue(ycreCost.getCopeWith() == null ? "0" : "(￥" + ycreCost.getCopeWith() + ".00)");
                    row.createCell(7).setCellValue(ycreCost.getCostType() == null ? "" : ycreCost.getCostType());
                    row.createCell(8).setCellValue(ycreCost.getClientName() == null ? "" : ycreCost.getClientName());
                    row.createCell(10).setCellValue(ycreCost.getPaymentTime() == null ? "" : ycreCost.getPaymentTime().format(formatter));
                    row.createCell(12).setCellValue(ycreCost.getCollectionTime() == null ? 0 : ycreCost.getCollectionTime());
                    row.createCell(13).setCellValue(ycreCost.getCommission() == null ? "0" : "(￥" + ycreCost.getCommission() + ".00)");
                    row.createCell(14).setCellValue(ycreCost.getCommissionRate() == null ? 0 : ycreCost.getCommissionRate());

                }
            }
            i++;

            row = sheet.createRow(i);
            row.createCell(0).setCellValue(company.getLocationId() == null ? "" : company.getLocationId());
            row.createCell(1).setCellValue(company.getSelesman() == null ? "" : company.getSelesman());
            row.createCell(2).setCellValue(company.getOperation() == null ? "" : company.getOperation());
            row.createCell(3).setCellValue(company.getItemName() == null ? "" : company.getItemName());
            row.createCell(4).setCellValue(company.getCarriageNum() == null ? "" : company.getCarriageNum());
            row.createCell(9).setCellValue(company.getProfit() == null ? "0" : "(￥" + company.getProfit() + ".00)");
            row.createCell(11).setCellValue(company.getOperationCost() == null ? "0" : "(￥" + company.getOperationCost() + ".00)");
            row.createCell(15).setCellValue(company.getOpenAccount() == null ? "" : company.getOpenAccount());
            row.createCell(16).setCellValue(company.getRemarkInfo() == null ? "" : company.getRemarkInfo());

            if (company.getServiceman() != null && !company.getServiceman().isEmpty()) {
                i++;

                row = sheet.createRow(i);
                row.createCell(0).setCellValue(company.getLocationId() == null ? "" : company.getLocationId());
                row.createCell(1).setCellValue(company.getServiceman());

            }
        }


        //列宽自适应
        for (int j = 0; j <= 16; j++) {
            sheet.autoSizeColumn(j);
        }

        // 针对IE或者以IE为内核的浏览器：
        String userAgent = request.getHeader("User-Agent").toLowerCase();
        String fileName = sheetName + ".xlsx";
        if (userAgent.contains("msie") || userAgent.contains("trident")) {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } else {
            // 非IE浏览器的处理：
            fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
        }
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("success", "true");
        OutputStream os = response.getOutputStream();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);//默认Excel名称

        wb.write(os);
        os.flush();
        os.close();

        return "导出信息成功";
    }

    private Integer getRowNum(Sheet sheet) {
        int totalRows = sheet.getPhysicalNumberOfRows();
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                return r;
            }
            String companyName = ExcelUtil.getCellValue(row.getCell(0));
            String creditCode = ExcelUtil.getCellValue(row.getCell(1));
            if (companyName.trim().equals("") && creditCode.trim().equals("")) {
                return r;
            }
        }
        return totalRows;
    }
/*
    private List<Company> getCompaniesFromExcel(Workbook wb) throws Exception {
        List<Company> companies = new ArrayList<>();
        *//** 得到第一个shell *//*
        Sheet sheet = wb.getSheetAt(0);
        *//** 得到Excel的行数 *//*
        int totalRows = getRowNum(sheet);
        List<String> creditCodes = new ArrayList<>();
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        *//** 循环Excel的行 *//*
        for (int r = 1; r < totalRows; r++) {
            Company company = new Company();
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            String cellValue = ExcelUtil.getCellValue(row.getCell(0));
            if (cellValue.trim().equals("")) {
                throw new Exception("企业名称不能为空,请确认!");
            }
            company.setName(cellValue);
            cellValue = ExcelUtil.getCellValue(row.getCell(1));
            if (cellValue.trim().equals("")) {
                throw new Exception("企业信用代码不能为空,请确认!");
            }
            if (creditCodes.contains(cellValue)) {
                throw new Exception("有重复的企业信用代码" + cellValue + ",请确认!");
            }
            creditCodes.add(cellValue);
            company.setCreditCode(cellValue);
            cellValue = ExcelUtil.getCellValue(row.getCell(2));
            if (cellValue.trim().length() > 0) {
                company.setContactName(cellValue);
            }
            cellValue = ExcelUtil.getCellValue(row.getCell(3));
            if (cellValue.trim().length() > 0) {
                company.setContactPhone(cellValue);
            }
            cellValue = ExcelUtil.getCellValue(row.getCell(4));
            company.setCorporation(cellValue);
            cellValue = ExcelUtil.getCellValue(row.getCell(5));
            if (Helpers.isNotNullAndEmpty(cellValue)) {
                if (!Validator.isEmail(cellValue)) {
                    throw new Exception("邮箱格式不正确" + cellValue + ",请确认!");
                }
                company.setEmail(cellValue);
            }
            cellValue = ExcelUtil.getCellValue(row.getCell(6));
            if (Helpers.isNotNullAndEmpty(cellValue)) {
                if (!Validator.isFax(cellValue)) {
                    throw new Exception("传真格式不正常" + cellValue + ",请确认!");
                }
                company.setFax(cellValue);
            }
            cellValue = ExcelUtil.getCellValue(row.getCell(7));
            company.setAddress(cellValue);
            cellValue = ExcelUtil.getCellValue(row.getCell(8));
            Double area = cellValue.equals("") ? 0 : Double.valueOf(cellValue);
            if (area < 0) {
                throw new Exception("占地面积不能为负数" + cellValue + ",请确认!");
            }
            company.setFloorArea(area);

            companies.add(company);
        }
        List<Company> companyList = companyRepository.findCompaniesByCreditCodeIn(creditCodes);
        if (companyList != null && companyList.size() > 0) {
            List<String> list = companyList.stream().map(Company::getCreditCode).collect(Collectors.toList());
            throw new Exception("以下信用代码已存在：" + String.join(",", list));
        }
        return companies;
    }*/

}
